﻿CREATE PROCEDURE [audit].[DefineTrace]
@TraceName VARCHAR (256), @TraceID INT
AS
BEGIN
	DECLARE @RC int, @ERROR int
	SET @RC = 0
	SET @ERROR = 0

	DECLARE TRACE_CURSOR CURSOR READ_ONLY LOCAL FAST_FORWARD FOR(
		SELECT
			[Event]
			,[Column]
			,[Filter]
			,[LogicalOperator]
			,[ComparisonOperator]
			,[FilterValue]
		FROM [config].[TraceDefinitions]
		WHERE([TraceName] = @TraceName)
		AND  ([Column] NOT IN ('EventClass')) --DEFAULT COLUMNS
	)
	OPEN TRACE_CURSOR

	DECLARE @Event varchar(50)
		,@Column nvarchar(50)
		,@Filter nvarchar(50)
		,@LogicalOperator char(3)
		,@ComparisonOperator char(8)
		,@FilterValue sql_variant

	FETCH NEXT FROM TRACE_CURSOR INTO @Event, @Column, @Filter, @LogicalOperator, @ComparisonOperator, @FilterValue

	DECLARE @EventNumber int, @ColumnNo int

	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			SET @ColumnNo = audit.GetTraceColNo(@Column)

			IF(@Event IS NOT NULL)
				BEGIN
					SET @EventNumber = audit.GetTraceEventNo(@Event)

					DECLARE @On bit
					SET @On = 1

					EXEC @RC = master.dbo.sp_trace_setevent @TraceID, @EventNumber, @ColumnNo, @On
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0)
					OR(@RC <> 0)
						GOTO ERROR_HANDLER
				END
			ELSE IF(@Filter IS NOT NULL)
				BEGIN
					DECLARE @LogicalOperatorNo int
					SET @LogicalOperatorNo = audit.GetTraceLogicalOperatorNo(@LogicalOperator)

					DECLARE @ComparisonOperatorNo int
					SET @ComparisonOperatorNo = audit.GetTraceComparisonOperatorNo(@ComparisonOperator)

					DECLARE @ColFilterType nvarchar(128)
					SET @ColFilterType = audit.GetTraceColFilterType(@ColumnNo)

					DECLARE @Statement nvarchar(256)
					SET @Statement = N'DECLARE @FilterValue AS ' + @ColFilterType + '
						SET @FilterValue = CAST(@VariantValue AS ' + @ColFilterType + ')
						EXEC @RC = master.dbo.sp_trace_setfilter ' + CAST(@TraceID AS nvarchar) + '
							, ' + CAST(@ColumnNo AS nvarchar) + '
							, ' + CAST(@LogicalOperatorNo AS nvarchar) + '
							, ' + CAST(@ComparisonOperatorNo AS nvarchar) + '
							, @FilterValue
						RETURN(@RC)'

					EXEC @RC = sp_executesql @Statement
						,'@VariantValue sql_variant'
						,@VariantValue = @FilterValue
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0)
					OR(@RC <> 0)
						GOTO ERROR_HANDLER
				END

		FETCH NEXT FROM TRACE_CURSOR INTO @Event, @Column, @Filter, @LogicalOperator, @ComparisonOperator, @FilterValue
	END
	
	PRINT 'Trace defined successfully'
	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC <> 0)
		BEGIN
			DECLARE @ERROR_MESSAGE nvarchar(256)
			SET @ERROR_MESSAGE = audit.GetTraceReturnCodeValue(@RC) + NCHAR(10) +
			'@Event = ' + ISNULL(CAST(@EventNumber AS nvarchar),N'') + 
			', @Column = ' + ISNULL(CAST(@ColumnNo AS nvarchar),N'') + 
			', @Filter = ' + ISNULL(@Filter,N'') + 
			', @LogicalOperator = ' + ISNULL(@LogicalOperator,N'') + 
			', @ComparisonOperator = ' + ISNULL(@ComparisonOperator,N'') + 
			', @FilterValue = ' + ISNULL(CAST(@FilterValue AS nvarchar),N'')		
			RAISERROR(@ERROR_MESSAGE,16,1)
		END
	ELSE
		SET @RC = @ERROR
	
	RAISERROR('Failed to define trace',16,1)

EXIT_PROC:
	IF (CURSOR_STATUS('local','TRACE_CURSOR') >= 0)
		BEGIN
			CLOSE TRACE_CURSOR
			DEALLOCATE TRACE_CURSOR
		END

	RETURN(@RC)
END